package com.enterprise.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.enterprise.dto.ItemBean;
import com.enterprise.dto.LinkBean;

public class DBUitl {
	
	public static   Connection getConnection()
	{

		 try {
				Class.forName("org.sqlite.JDBC");
			} catch (ClassNotFoundException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}   
			
		    
		    Connection conn = null;
		    Statement stat = null ;
			try {
				conn = DriverManager.getConnection("jdbc:sqlite:itdata.db");
			}catch (Exception e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}   
			return conn;
	}
	
	public static void createTable()
	{
		    
		    Connection conn =getConnection();
		    try {
		
		
	        Statement stat=null;
	         stat = conn.createStatement();   
	        
	        String sql= "CREATE TABLE [company] ("
	    			+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT, "
	    			+ "[name] TEXT, " + "[pinyin] TEXT) ";
	        stat.execute("DROP TABLE if exists  [company] ");
	        stat.executeUpdate(sql);    
	        conn.close();
	        stat.close();
	         }
	        catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}   
			
	}
	
	
	
	public static void createTableItem()
	{
		    
		    Connection conn =getConnection();
		    try {
		
		
	        Statement stat=null;
	         stat = conn.createStatement();   
	        
	        String sql= "CREATE TABLE [item] ("
	    			+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT, "
	    			+ "[title] TEXT, " + "[content] TEXT, " + "[company_id] INTEGER) ";
	        stat.execute("DROP TABLE if exists  [item] ");
	        stat.executeUpdate(sql);    
	        conn.close();
	        stat.close();
	         }
	        catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}   
			
	}
	
	public static void createTableLink()
	{
		    
		    Connection conn =getConnection();
		    try {
		
		
	        Statement stat=null;
	         stat = conn.createStatement();   
	        
	        String sql= "CREATE TABLE [link] ("
	    			+ "[_id] INTEGER PRIMARY KEY AUTOINCREMENT, "
	    			+ "[url] TEXT, " + "[status] INTEGER, "+ "[company_id] INTEGER) ";
	        stat.execute("DROP TABLE if exists  [link] ");
	        stat.executeUpdate(sql);    
	        conn.close();
	        stat.close();
	         }
	        catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}   
			
	}
	
	public static void InserData(List<LinkBean> alist) throws SQLException
	{
		Connection conn =getConnection();
		 Statement stat=null;
		 String sql = null;
         stat = conn.createStatement();   
		for(LinkBean lb:alist)
		{
			String pinyin = lb.getHref();
			pinyin= pinyin.substring(pinyin.lastIndexOf("/")+1,pinyin.length());
					
					
			sql = "INSERT INTO [company]("
					+ "[name],[pinyin])"
					+ " values ('" + lb.getHtml()+ "','" + pinyin+"');";
			stat.executeUpdate(sql);
		}
		conn.close();
		stat.close();
	}
    
	
	public static void InserDataItems(List<ItemBean> alist) throws SQLException
	{
		Connection conn =getConnection();
		 Statement stat=null;
		 String sql = null;
         stat = conn.createStatement();   
		for(ItemBean lb:alist)
		{
			
			
		System.out.println("title="+lb.getTitle()+" companyid="+lb.getCompanyid());
					
			sql = "INSERT INTO [item]("
					+ "[title],[content],[company_id])"
					+ " values ('" + lb.getTitle()+ "','" + lb.getContent()+"','"+lb.getCompanyid()+"');";
			stat.executeUpdate(sql);
			
			}
		conn.close();
		stat.close();
	}
	
	public static void InserDataLink(ItemBean lb) throws SQLException
	{
		Connection conn =getConnection();
		 Statement stat=null;
		 String sql = null;
         stat = conn.createStatement();   
		
			
		System.out.println(" companyid="+lb.getCompanyid()+"   url="+lb.getUrl());
					
			sql = "INSERT INTO [link]("
					+ "[url],[status],[company_id])"
					+ " values ('" + lb.getUrl()+ "',0,'"+lb.getCompanyid()+"');";
			stat.executeUpdate(sql);
		
		conn.close();
		stat.close();
	}
	
	public static void InserDataItem(ItemBean lb) throws SQLException
	{
		Connection conn =getConnection();
		 Statement stat=null;
		 String sql = null;
         stat = conn.createStatement();   
		
			
			
		System.out.println("title="+lb.getTitle()+" companyid="+lb.getCompanyid());
					
			sql = "INSERT INTO [item]("
					+ "[title],[content],[company_id])"
					+ " values ('" + lb.getTitle()+ "','" + lb.getContent()+"','"+lb.getCompanyid()+"');";
			stat.executeUpdate(sql);
		   sql = "UPdate  [link]"
				+ "set [status]=1"
				+ " where [_id] =" + lb.getLinkid()+";";
		stat.executeUpdate(sql);
		conn.close();
		stat.close();
	}
	
	public static ItemBean getUnParseLink() throws SQLException
	{
		Connection conn =getConnection();
		 Statement stat=null;
		 String sql = null;
        stat = conn.createStatement();   
		
			
			
					
			sql = "SELECT * FROM [link] WHERE status=0 Order BY _ID limit 1";
					
			ResultSet  rs=stat.executeQuery(sql);
		  ItemBean it=null;
			
			while (rs.next()) {
				 it= new ItemBean();
				it.setUrl(rs.getString("url"));
				it.setCompanyid(rs.getInt("company_id"));
			    it.setLinkid(rs.getInt("_id"));
			    }
        rs.close();
		conn.close();
		
		return it;
		
		
		
	}
	public static List<ItemBean>  getDataItems(int id) throws SQLException
	{
 
		Connection conn =getConnection();
		 Statement stat=null;
		 String sql = null;
         stat = conn.createStatement(); 
          List<ItemBean> itemList =new ArrayList<ItemBean>();
         sql = "SELECT title, content, company_id,_id FROM [item]  where company_id="+id+" order by _id desc";
			
			ResultSet  rs=stat.executeQuery(sql);
		  ItemBean it=null;
			
			while (rs.next()) {
				 it= new ItemBean();
				it.setUrl(rs.getString("title"));
				it.setCompanyid(rs.getInt("company_id"));
			    it.setLinkid(rs.getInt("_id"));
			    it.setTitle(rs.getString("title"));
			    itemList.add(it);
			    }
    
		
		conn.close();
		stat.close();
	return itemList;

}

}
